'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const { INTEGER, STRING, DECIMAL, DATE, ENUM } = Sequelize;
    
    await queryInterface.createTable('sales_orders', {
      id: {
        type: INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      platform_order_id: {
        type: STRING(200),
        allowNull: false,
        unique: true,
        comment: '平台订单ID'
      },
      customer_id: {
        type: INTEGER,
        allowNull: false,
        comment: '客户ID'
      },
      order_amount: {
        type: DECIMAL(12, 2),
        allowNull: false,
        comment: '订单金额'
      },
      order_status: {
        type: ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),
        defaultValue: 'pending',
        comment: '订单状态'
      },
      order_date: {
        type: DATE,
        allowNull: false,
        comment: '订单日期'
      },
      platform_source: {
        type: ENUM('zcy', 'manual'),
        defaultValue: 'zcy',
        comment: '订单来源'
      },
      invoice_status: {
        type: ENUM('uninvoiced', 'invoiced', 'sent'),
        defaultValue: 'uninvoiced',
        comment: '开票状态'
      },
      invoice_date: {
        type: DATE,
        comment: '开票日期'
      },
      responsible_salesman: {
        type: STRING(100),
        comment: '负责销售'
      },
      created_at: {
        type: DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        type: DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
      }
    });

    // 添加索引
    await queryInterface.addIndex('sales_orders', ['customer_id', 'order_date'], {
      name: 'sales_orders_idx_customer_date',
      unique: false
    });
    
    await queryInterface.addIndex('sales_orders', ['platform_order_id'], {
      name: 'sales_orders_idx_platform_order',
      unique: false
    });
    
    await queryInterface.addIndex('sales_orders', ['invoice_status'], {
      name: 'sales_orders_idx_invoice_status',
      unique: false
    });

    // 添加外键约束
    await queryInterface.addConstraint('sales_orders', {
      fields: ['customer_id'],
      type: 'foreign key',
      name: 'sales_orders_fk_customer_id',
      references: {
        table: 'customers',
        field: 'id'
      },
      onDelete: 'cascade',
      onUpdate: 'cascade'
    });
  },

  down: async (queryInterface, Sequelize) => {
    // 先删除外键约束
    await queryInterface.removeConstraint('sales_orders', 'sales_orders_fk_customer_id');
    
    // 删除索引
    await queryInterface.removeIndex('sales_orders', 'sales_orders_idx_customer_date');
    await queryInterface.removeIndex('sales_orders', 'sales_orders_idx_platform_order');
    await queryInterface.removeIndex('sales_orders', 'sales_orders_idx_invoice_status');
    
    await queryInterface.dropTable('sales_orders');
  }
};